from django.shortcuts import render, redirect, get_object_or_404
from django.contrib.auth.decorators import login_required
from django.contrib import messages
from django.db import connection, transaction
from django.utils import timezone
import json

from .models import RewardItem, PointRecord, PointsExchange

@login_required
def index(request):
    """积分商城首页"""
    # 获取用户积分
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT mq_points FROM tb_user WHERE user_id = %s
        """, [request.user.user_id])
        user_points = cursor.fetchone()[0] or 0
    
    # 获取积分商品列表
    rewards = RewardItem.objects.filter(status=1).order_by('points_required')
    
    # 用户已兑换的记录
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT reward_id, COUNT(*) 
            FROM tb_points_exchange 
            WHERE user_id = %s AND status IN (1, 2)
            GROUP BY reward_id
        """, [request.user.user_id])
        exchanged = {row[0]: row[1] for row in cursor.fetchall()}
    
    context = {
        'user_points': user_points,
        'rewards': rewards,
        'exchanged': exchanged
    }
    return render(request, 'points/index.html', context)

@login_required
def history(request):
    """积分历史记录"""
    # 获取用户积分
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT mq_points FROM tb_user WHERE user_id = %s
        """, [request.user.user_id])
        user_points = cursor.fetchone()[0] or 0
    
    # 获取积分记录
    records = PointRecord.objects.filter(user_id=request.user.user_id).order_by('-create_time')[:50]
    
    context = {
        'user_points': user_points,
        'records': records
    }
    return render(request, 'points/history.html', context)

@login_required
@transaction.atomic
def exchange(request, reward_id):
    """积分兑换"""
    reward = get_object_or_404(RewardItem, reward_id=reward_id, status=1)
    
    # 获取用户积分
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT mq_points FROM tb_user WHERE user_id = %s FOR UPDATE
        """, [request.user.user_id])
        user_points = cursor.fetchone()[0] or 0
    
    # 检查积分是否足够
    if user_points < reward.points_required:
        messages.error(request, f'积分不足，还差{reward.points_required - user_points}积分')
        return redirect('points:index')
    
    # 检查库存
    if reward.stock <= 0:
        messages.error(request, '很抱歉，该奖品已兑完')
        return redirect('points:index')
    
    if request.method == 'POST':
        receive_info = {}
        
        # 实物奖品需要收货信息
        if reward.reward_type == 2:  # 实物奖品
            name = request.POST.get('name')
            phone = request.POST.get('phone')
            address = request.POST.get('address')
            
            if not (name and phone and address):
                messages.error(request, '请填写完整的收货信息')
                return render(request, 'points/exchange.html', {'reward': reward})
            
            receive_info = {
                'name': name,
                'phone': phone,
                'address': address
            }
        
        try:
            # 开始事务
            with transaction.atomic():
                # 1. 扣减用户积分
                with connection.cursor() as cursor:
                    cursor.execute("""
                        UPDATE tb_user 
                        SET mq_points = mq_points - %s 
                        WHERE user_id = %s
                    """, [reward.points_required, request.user.user_id])
                
                # 2. 添加积分记录
                with connection.cursor() as cursor:
                    cursor.execute("""
                        INSERT INTO tb_point_record 
                        (user_id, mq_points, type, reference_id, description, create_time)
                        VALUES (%s, %s, %s, %s, %s, %s)
                    """, [
                        request.user.user_id,
                        -reward.points_required,
                        4,  # 商品兑换
                        reward.reward_id,
                        f'兑换 {reward.name}',
                        timezone.now()
                    ])
                
                # 3. 减少库存
                with connection.cursor() as cursor:
                    cursor.execute("""
                        UPDATE tb_reward_item
                        SET stock = stock - 1
                        WHERE reward_id = %s
                    """, [reward.reward_id])
                
                # 4. 创建兑换记录
                with connection.cursor() as cursor:
                    cursor.execute("""
                        INSERT INTO tb_points_exchange
                        (user_id, reward_id, points_used, exchange_time, status, receive_info)
                        VALUES (%s, %s, %s, %s, %s, %s)
                    """, [
                        request.user.user_id,
                        reward.reward_id,
                        reward.points_required,
                        timezone.now(),
                        1,  # 待处理
                        json.dumps(receive_info) if receive_info else None
                    ])
                
                # 5. 处理特殊奖品类型
                if reward.reward_type == 3:  # 妙趣徽章
                    # 更新用户的徽章数量
                    with connection.cursor() as cursor:
                        cursor.execute("""
                            UPDATE tb_user 
                            SET badge_count = badge_count + 1 
                            WHERE user_id = %s
                        """, [request.user.user_id])
            
            messages.success(request, f'恭喜您，成功兑换 {reward.name}')
            return redirect('points:exchanges')
        
        except Exception as e:
            messages.error(request, f'兑换失败: {str(e)}')
            return redirect('points:index')
    
    return render(request, 'points/exchange.html', {'reward': reward, 'user_points': user_points})

@login_required
def exchange_records(request):
    """我的兑换记录"""
    # 原生SQL查询兑换记录和对应奖品信息
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT pe.exchange_id, pe.reward_id, pe.points_used, pe.exchange_time, 
                   pe.status, pe.receive_info, ri.name, ri.image_url, ri.reward_type
            FROM tb_points_exchange pe
            JOIN tb_reward_item ri ON pe.reward_id = ri.reward_id
            WHERE pe.user_id = %s
            ORDER BY pe.exchange_time DESC
        """, [request.user.user_id])
        
        columns = [col[0] for col in cursor.description]
        exchanges = [dict(zip(columns, row)) for row in cursor.fetchall()]
    
    # 获取用户积分
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT mq_points FROM tb_user WHERE user_id = %s
        """, [request.user.user_id])
        user_points = cursor.fetchone()[0] or 0
    
    context = {
        'exchanges': exchanges,
        'user_points': user_points
    }
    return render(request, 'points/exchanges.html', context)
